SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




ALTER      trigger tb_invoiced_ad on tb_invoiced for delete as
begin
  set nocount on

  update tb_purchase set invamt=r.invamt-d.amount, relationcount=r.relationcount-1, checkno=replace(r.checkno,','+m.checkno,'')  
    , invbdate=case when r.invamt-d.amount=0 then null 
       else (select top 1 bdate from tb_invoice im,tb_invoiced id where im.bid=id.bid and id.relationbtype=140 and id.relationbid=d.relationbid) end  
    from tb_purchase r,deleted d,tb_invoice m where d.relationbtype=140 and r.bid=d.relationbid and m.bid=d.bid 
  update tb_purchase set invtype=null, invclose=0 
    from tb_purchase r,deleted d where d.relationbtype=140 and r.bid=d.relationbid 
      and not exists(select relationbid from tb_invoiced i where i.relationbtype=140 and i.relationbid=d.relationbid)

  update tb_sale set invamt=r.invamt-d.amount, relationcount=r.relationcount-1, checkno=replace(r.checkno,','+m.checkno,'')
    , invbdate=case when r.invamt-d.amount=0 then null 
       else (select top 1 bdate from tb_invoice im,tb_invoiced id where im.bid=id.bid and id.relationbtype=240 and id.relationbid=d.relationbid) end  
    from tb_sale r,deleted d,tb_invoice m where d.relationbtype=240 and r.bid=d.relationbid and m.bid=d.bid 
  update tb_sale set invtype=null, invclose=0--, invbdate=null 
    from tb_sale r,deleted d where d.relationbtype=240 and r.bid=d.relationbid 
      and not exists(select relationbid from tb_invoiced i where i.relationbtype=240 and i.relationbid=d.relationbid)

  update tb_purexpend set invamt=r.invamt-d.amount, relationcount=r.relationcount-1, checkno=replace(r.checkno,','+m.checkno,'')  
    from tb_purexpend r,deleted d,tb_invoice m where d.relationbtype=150 and r.bid=d.relationbid and m.bid=d.bid
  update tb_purexpend set invtype=null, invclose=0 
    from tb_purexpend r,deleted d where d.relationbtype=150 and r.bid=d.relationbid
      and not exists(select relationbid from tb_invoiced i where i.relationbtype=150 and i.relationbid=d.relationbid)

  update tb_svcshipment set invamt=r.invamt-d.amount, relationcount=r.relationcount-1, checkno=replace(r.checkno,','+m.checkno,'')  
    from tb_svcshipment r,deleted d,tb_invoice m where d.relationbtype=2030 and r.bid=d.relationbid and m.bid=d.bid
  update tb_svcshipment set invtype=null, invclose=0 
    from tb_svcshipment r,deleted d where d.relationbtype=2030 and r.bid=d.relationbid
      and not exists(select relationbid from tb_invoiced i where i.relationbtype=2030 and i.relationbid=d.relationbid)

  update tb_comexpenditure set invamt=r.invamt-d.amount, relationcount=r.relationcount-1, checkno=replace(r.checkno,','+m.checkno,'') 
    from tb_comexpenditure r,deleted d,tb_invoice m where d.relationbtype=1140 and r.bid=d.relationbid and m.bid=d.bid
  update tb_comexpenditure set invtype=null, closeinvoice=0 
    from tb_comexpenditure r,deleted d where d.relationbtype=1140 and r.bid=d.relationbid
      and not exists(select relationbid from tb_invoiced i where i.relationbtype=1140 and i.relationbid=d.relationbid)

  declare @relationbtype int,@relationbid int
  select @relationbtype=relationbtype, @relationbid=relationbid from deleted
  exec p_setbillinvclose @relationbtype, @relationbid
end





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

